package com.airui.dao;

import com.airui.domain.ZhangWu;
import com.airui.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class ZhangWuDao {
    QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
    String sql;
    String sql2;
    public List<ZhangWu> selectAll() {
        sql = "select * from airui_zhangwu";
        List<ZhangWu> list = null;
        try {
            list = qr.query(sql, new BeanListHandler<ZhangWu>(ZhangWu.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<ZhangWu> selectByCreateTime(String start, String end) {
        sql = "select * from airui_zhangwu where createtime between ? and ?";
        List<ZhangWu> list = null;
        Object[] params={start, end};
        try {
            list = qr.query(sql, new BeanListHandler<ZhangWu>(ZhangWu.class), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public void addZhangwu(ZhangWu zhangWu) {
        sql = "insert into airui_zhangwu values(null, ?, ?, ?, ?, ?)";
        Object[] params = {zhangWu.getFlname(), zhangWu.getMoney(), zhangWu.getZhanghu()
        , zhangWu.getCreatetime(), zhangWu.getDescription()};
        try {
            qr.update(sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public boolean editZhangWu(ZhangWu zhangWu) {
        sql = "select zwid from airui_zhangwu where zwid = ?";
        Object params = zhangWu.getZwid();
        try {
            if (qr.query(sql, new ScalarHandler<>(), params) != null){
                sql = "select * from airui_zhangwu where zwid = ?";
                params = zhangWu.getZwid();
                ZhangWu zhangWu1 = qr.query(sql, new BeanHandler<>(ZhangWu.class), params);
               zhangWu.setFlname(ifnull(zhangWu.getFlname(), zhangWu1.getFlname()));
               zhangWu.setCreatetime(ifnull(zhangWu.getCreatetime(), zhangWu1.getCreatetime()));
               zhangWu.setZhanghu(ifnull(zhangWu.getZhanghu(), zhangWu1.getZhanghu()));
               zhangWu.setDescription(ifnull(zhangWu.getDescription(), zhangWu1.getDescription()));

                sql = "UPDATE airui_zhangwu SET flname = ?, money = ?, zhanghu = ?, createtime = ?, description = ? WHERE zwid = ?";
                Object[] params1 = {zhangWu.getFlname(), zhangWu.getMoney(), zhangWu.getZhanghu(), zhangWu.getCreatetime(), zhangWu.getDescription(), zhangWu.getZwid()};
                qr.update(sql, params1);
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    private String ifnull(String zws, String zws1) {
        if (zws == null){
            return zws1;
        }
        return zws;
    }


    public boolean deleteZhangWu(int nextInt) {
        sql = "delete from airui_zhangwu where zwid = ?";
        Object params = nextInt;
        try {
            if (qr.update(sql, params) == 1){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    public List<ZhangWu> searchZhangWu(String next) {
        List<ZhangWu> zwlist = null;
        sql = "select * from airui_zhangwu where flname like ?";
        Object params = "%"+next+"%";
        try {
            zwlist = qr.query(sql, new BeanListHandler<>(ZhangWu.class), params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return zwlist;
    }
}
